using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public AgendaVO getAgenda(int? idAgenda, string assunto)
		{
			AgendaVO agendaVO = null;
			List<AgendaVO> listAgendaVOs = getAgendas(idAgenda, assunto, null, null, null);
			if (listAgendaVOs != null && listAgendaVOs.Count > 0)
			{
				agendaVO = listAgendaVOs[0];
			}
			return agendaVO;
		}

		public AgendaVO getAgenda(int? idAgenda)
		{
			return getAgenda(idAgenda, null);
		}

		public List<AgendaVO> getAgendas()
		{
			return getAgendas(null, null, null, null, null);
		}

		public List<AgendaVO> getAgendas(int? idAgenda)
		{
			return getAgendas(idAgenda, null, null, null, null);
		}

		public List<AgendaVO> getAgendas(int? idAgenda, string assunto)
		{
			return getAgendas(idAgenda, assunto, null, null, null);
		}

		public List<AgendaVO> getAgendas(int? idAgenda, string assunto, int? idFilial, string local, DateTime? data)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT agd.idAgenda as agd_idAgenda, agd.idFilial as agd_idFilial, fil.filial as fil_filial, agd.assunto as agd_assunto, agd.local as agd_local, agd.data as agd_data, agd.inicio as agd_inicio, agd.fim as agd_fim, agd.cadastro as agd_cadastro, agd.cadastradoPor as agd_cadastradoPor, agd.edicao as agd_edicao, agd.editadoPor as agd_editadoPor  ");
			query.AppendLine("FROM agenda as agd LEFT OUTER JOIN filial as fil on agd.idFilial=fil.idFilial  ");
			if (idAgenda != null)
			{
				query.AppendLine(where + "agd.idAgenda=@idAgenda ");
				where = "AND ";
			}
			if (idFilial != null)
			{
				query.AppendLine(where + "agd.idFilial=@idFilial ");
				where = "AND ";
			}
			if (assunto != null)
			{
				query.AppendLine(where + "agd.assunto LIKE @assunto ");
				where = "AND ";
			}
			if (local != null)
			{
				query.AppendLine(where + "agd.local=@local ");
				where = "AND ";
			}
			if (data != null)
			{
				query.AppendLine(where + "agd.data=@data ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY agd.assunto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idAgenda!= null) cmd.Parameters.Add(getParameter(cmd, "@idAgenda", idAgenda));
			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));
			if (assunto!= null) cmd.Parameters.Add(getParameter(cmd, "@assunto", "%"+assunto+"%"));
			if (local!= null) cmd.Parameters.Add(getParameter(cmd, "@local", local));
			if (data!= null) cmd.Parameters.Add(getParameter(cmd, "@data", data));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<AgendaVO> listAgenda = new List<AgendaVO>();

				while (reader.Read())
				{
					AgendaVO agendaVO = AgendaVO.CreateVO();
					agendaVO.idAgenda = reader.GetInt32(reader.GetOrdinal("agd_idAgenda"));
					agendaVO.idFilial = reader.GetInt32(reader.GetOrdinal("agd_idFilial"));
					if (!reader.IsDBNull(reader.GetOrdinal("fil_filial")))
						agendaVO.idFilialVO.filial = reader.GetString(reader.GetOrdinal("fil_filial"));
					agendaVO.assunto = reader.GetString(reader.GetOrdinal("agd_assunto"));
					if (!reader.IsDBNull(reader.GetOrdinal("agd_local")))
						agendaVO.local = reader.GetString(reader.GetOrdinal("agd_local"));
					agendaVO.data = reader.GetDateTime(reader.GetOrdinal("agd_data"));
					agendaVO.inicio = getTimeSpanFromLongTicks(reader, "agd_inicio");
					agendaVO.fim = getTimeSpanFromLongTicks(reader, "agd_fim");
					agendaVO.cadastro = reader.GetDateTime(reader.GetOrdinal("agd_cadastro"));
					agendaVO.cadastradoPor = reader.GetString(reader.GetOrdinal("agd_cadastradoPor"));
					agendaVO.edicao = reader.GetDateTime(reader.GetOrdinal("agd_edicao"));
					agendaVO.editadoPor = reader.GetString(reader.GetOrdinal("agd_editadoPor"));


					listAgenda.Add(agendaVO);
				}
				reader.Close();
				return listAgenda;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setAgenda(AgendaVO agendaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE agenda ");
			query.AppendLine("SET idFilial=@idFilial, assunto=@assunto, local=@local, data=@data, inicio=@inicio, fim=@fim,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idAgenda=@idAgenda ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idAgenda", agendaVO.idAgenda));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", agendaVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@assunto", agendaVO.assunto));
			if (agendaVO.local == null)
				cmd.Parameters.Add(getParameter(cmd, "@local", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@local", agendaVO.local));
			cmd.Parameters.Add(getParameter(cmd, "@data", agendaVO.data));
			cmd.Parameters.Add(getParameter(cmd, "@inicio", agendaVO.inicio));
			cmd.Parameters.Add(getParameter(cmd, "@fim", agendaVO.fim));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", agendaVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", agendaVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", agendaVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", agendaVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newAgenda(AgendaVO agendaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO agenda ");
			query.AppendLine("  ( idFilial, assunto, local, data, inicio, fim, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @idFilial, @assunto, @local, @data, @inicio, @fim, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idFilial", agendaVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@assunto", agendaVO.assunto));
			if (agendaVO.local == null)
				cmd.Parameters.Add(getParameter(cmd, "@local", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@local", agendaVO.local));
			cmd.Parameters.Add(getParameter(cmd, "@data", agendaVO.data));
			cmd.Parameters.Add(getParameter(cmd, "@inicio", agendaVO.inicio));
			cmd.Parameters.Add(getParameter(cmd, "@fim", agendaVO.fim));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", agendaVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", agendaVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", agendaVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", agendaVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('agenda') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delAgenda(AgendaVO agendaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM agenda ");
			query.AppendLine("WHERE idAgenda=@idAgenda ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idAgenda", agendaVO.idAgenda));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountAgendaxIdFilial(int? idFilial)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFilial) as ocorrencias FROM agenda WHERE IdFilial=@IdFilial ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFilial", idFilial));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
